﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.SqlTypes;

namespace LibreriaDeClases
{
    class InsumoBD
    {
                
        public static Insumo Get(Insumo item)
        {
            
            GestorDeConexion gdc = new GestorDeConexion();
            SqlConnection con = gdc.getConexion();
            SqlCommand com = new SqlCommand();
            SqlDataReader dr;
            String sql = "SELECT * FROM item WHERE idItem = @idItem";
            com.Connection = con;
            com.CommandText = sql;
            com.CommandType = CommandType.Text;
            com.Parameters.Add(new SqlParameter("@idItem", item.IdInsumo));
            con.Open();
            dr = com.ExecuteReader();

            if (dr.Read())
            {
                item = setItem(dr);
            }

            else
            {
                item = null;
            }

            dr.Close();
            con.Close();
            con.Dispose();

            return item;
        }

        public static Insumo Update(Insumo item)
        {
            GestorDeConexion gdc = new GestorDeConexion();
            SqlConnection con = gdc.getConexion();
            SqlCommand com = new SqlCommand();
            
            String sql = "UPDATE item SET nombre = @nombre, descripcion = @descripcion WHERE idItem = @idItem";
            com.Connection = con;
            com.CommandText = sql;
            com.CommandType = CommandType.Text;
            com.Parameters.Add(new SqlParameter("@idItem", item.IdInsumo));
            com.Parameters.Add(new SqlParameter("@descripcion", item.Descripcion == null ? SqlString.Null : item.Descripcion));
            com.Parameters.Add(new SqlParameter("@nombre", item.Nombre == null ? SqlString.Null : item.Nombre));

            con.Open();
            int res = com.ExecuteNonQuery();
            
            if (res<1)
            {
                item = null;
            }            
            
            con.Close();
            con.Dispose();

            return item;
        }


        public static void Insert(Insumo item)
        {
            GestorDeConexion gdc = new GestorDeConexion();
            SqlConnection con = gdc.getConexion();
            SqlCommand com = new SqlCommand();
            
            String sql = "INSERT INTO item (descripcion, nombre) "+
                         "VALUES (@nombre,@descripcion)";
            com.Connection = con;
            com.CommandText = sql;
            com.CommandType = CommandType.Text;
            com.Parameters.Add(new SqlParameter("@nombre", item.Nombre == null ? SqlString.Null : item.Nombre));
            com.Parameters.Add(new SqlParameter("@descripcion", item.Descripcion == null ? SqlString.Null : item.Descripcion));
            con.Open();

            int NumReg = com.ExecuteNonQuery();

            if (NumReg < 1)
            {
                item = null;
            }

            else
            {
                item = null;
            }            
            con.Close();
            con.Dispose();

            
        }

        public static bool Delete(Insumo item)
        {
            GestorDeConexion gdc = new GestorDeConexion();
            SqlConnection con = gdc.getConexion();
            SqlCommand com = new SqlCommand();

            String sql = "DELETE FROM item WHERE itemId = @itemId";
            com.Connection = con;
            com.CommandText = sql;
            com.CommandType = CommandType.Text;
            com.Parameters.Add(new SqlParameter("@itemId", item.IdInsumo));
            con.Open();

            int NumReg = com.ExecuteNonQuery();

            con.Close();
            con.Dispose();

            if (NumReg > 0)
            {
                return true;
            }

            else
            {
                return false;
            }
        }

        public static List<Insumo> GetList()
        {
            GestorDeConexion gdc = new GestorDeConexion();
            SqlConnection con = gdc.getConexion();
            SqlCommand com = new SqlCommand();
            SqlDataReader dr;
            String sql = "SELECT * FROM item";
            List<Insumo> items = new List<Insumo>();
            com.Connection = con;
            com.CommandText = sql;
            com.CommandType = CommandType.Text;            
            con.Open();
            dr = com.ExecuteReader();

            while (dr.Read())
            {
                Insumo item = setItem(dr);
                items.Add(item);
            }
            
            dr.Close();
            con.Close();
            con.Dispose();

            return items;
        }
        private static Insumo setItem(SqlDataReader dr)
        {
            Insumo item = new Insumo();

            item.IdInsumo = (int)dr["idInsumo"];
            item.Nombre = DBNull.Value.Equals(dr["nombre"]) ? "" : (String)dr["nombre"];
            item.Descripcion = DBNull.Value.Equals(dr["descripcion"]) ? "" : (String)dr["descripcion"]; 

            return item;
        }
    }
}

